from copy import copy

import openpyxl
from openpyxl.styles import PatternFill
import os
import re
def delete_file(file_path):   # 删除文件
    # 检查文件是否存在
    if os.path.isfile(file_path):
        # 删除文件
        os.remove(file_path)
        print(f"文件 {file_path} 已被删除。")
    else:
        print(f"文件 {file_path} 不存在。")
def seclect_sheet(excel1, excel2):    # 选择sheet表
    # 打开Excel文件
    global workbook, workbook2, workbook3  # 全局变量工作簿对象
    workbook = openpyxl.load_workbook(excel1)
    workbook2 = openpyxl.load_workbook(excel2)
    workbook3 = openpyxl.Workbook()

    # 获取表名
    excel1_name = excel1.split('\\')[-1]
    excel2_name = excel2.split('\\')[-1]
    sheetname1 = workbook.sheetnames
    sheetname2 = workbook2.sheetnames
    # 回显菜单
    print(f"{excel1_name}的sheet表有：")
    count = 0  # sheet编号
    dict1_sheet = {}  # 将sheet名和数字键值对应
    for name in sheetname1:
        count += 1
        dict1_sheet.update({count:name})
        print(f"{name}\t{count}")
    print(f"{excel2_name}的sheet表有：")
    count = 0 # sheet编号
    dict2_sheet = {}
    for name in sheetname2:
        count += 1
        dict2_sheet.update({count:name})
        print(f"{name}\t{count}")
    # 选择工作表
    global str
    str = input("请输入需要比较的sheet表编号以空格隔开：")  # 从键盘获取sheet编号以选择对应的sheet表
    if str == "exit":     # 退出程序
        delete_file(f'{path}time.xlsx')
        exit()
    elif str == "":
        print("请输入sheet表编号！")
        delete_file(f'{path}time.xlsx')
        exit()
    else:
    # for num in range(1, int(str.split(' ')[-1]) + 1):
    #     # global str(num)+"worksheet_execl"
    #     exec(f"global worksheet_execl{num}")
        global worksheet_excel1, worksheet_excel2    # 获取工作表
        worksheet_excel1 = workbook[dict1_sheet[int(str.split(' ')[0])]]
        worksheet_excel2 = workbook2[dict2_sheet[int(str.split(' ')[1])]]
        return worksheet_excel1, worksheet_excel2

def column_all(sheet):  # 字典数据  格式：{列[A1]:[value1, value2]}
    # 取出表格所有元素，并以列：值生成字典，方便后续取用
    column_date = {}
    for column in sheet.iter_cols(min_col=1, max_col=sheet.max_column, values_only=True):  # 遍历第一行有值单元格，并获取值
        column1_values = column[0]  # 获取第一行表头
        if column1_values == None:   # 根据表头值为空排除空列
            break
        else:
            for row in sheet.iter_rows():        # 遍历所有单元格
                for cell in row:
                    if cell.value == column1_values:  # 根据单元格值与表头值相同，获取该列所有值
                        # 获取坐标
                        row_number = cell.coordinate  # A1, A2...
                        # 获取列号
                        column_num = row_number[0]    # A, B...
                        # 通过获取到的列号加表头值生成字典  {value:[value1, value2...]} 或 {A:[value1, value2...]}
                        column_date.update({column1_values:[val.value for val in sheet[column_num] if val.value != None] })
    return column_date  # 返回字典
def col_all():  # 统一MAC格式为纯字母数字，并获得行号字典{num_line:value}

    global column_list, mac_dky_dict, mac_mf_dict
    column_list = []  # 存储sheet表信息，并以列表展示[sheet1, sheet2...]
    for sheet in worksheet_excel1, worksheet_excel2:
        column_list.append(column_all(sheet))
    # 获得MAC地址
    mac_dky = column_list[0]["使用者CN"]
    mac_mf = column_list[1]["MAC地址信息（对应附表1）"]
    global cleaned_list_dky, cleaned_list_mf, mac_mf_dict, mac_dky_dict
    # 统一特定格式
    cleaned_list_dky = [s.strip("\u200e") for s in mac_dky]
    cleaned_list_mf = [s.strip("\u200e") for s in mac_mf]
    cleaned_list_dky = [s.replace("_", "").upper() for s in
                        [re.sub(r'-', "", dky) for dky in cleaned_list_dky]]
    cleaned_list_mf = [mf.replace("_", "").upper() for mf in
                       [re.sub(r'-', "", dky) for dky in cleaned_list_mf]]

#   获得{行号: mac}字典
    mac_dky_dict = {}
    mac_mf_dict = {}
    count_dky = 0
    for mac in cleaned_list_dky:
        count_dky += 1
        mac_dky_dict.update({count_dky:mac})
    count_mf = 0
    for mac in cleaned_list_mf:
        count_mf += 1
        mac_mf_dict.update({count_mf:mac})

def time_dky():   # 通过电科院的MAC地址获取对应时间，与密服的MAC地址比对形成{行号:[mac， 时间]}字典
    global column_list, mac_time
    mac_time = {}  # dky行号时间字典{行号：时间}
    count = 0
    mac_match = []
    # mac_diff = []
    for mac in cleaned_list_dky:
        count +=1
        mac_time.update({count:column_list[0]["证书到期时间"][count -1]})    #  {行号：time}
        if mac in cleaned_list_mf:
            mac_match.append(mac)  # 相同的MAC地址
        # else:
        #     mac_diff.append(mac)   # 不同的MAC地址

    keys_dky = []
    keys_mf = []
    for key, val in mac_dky_dict.items():   #  获取dky表中相同mac对应的行号
        for value in mac_match:
            if val == value:
                keys_dky.append(key)
                break
    for key, val in mac_mf_dict.items():    #  获取mf表中下相同mac对应的行号
        for value in mac_match:
            if val == value:
                keys_mf.append(key)
                break


    # num_time_mac = {}
    global num_time_mac_mf
    num_time_mac_mf = {}  # {行号：【mac，时间】}
    for num in keys_dky:
        #1.拿到dky表中mac对应的行号以及时间
        # num_time_mac.update({num:[mac_dky_dict[num], mac_time[num]]})
        #2.通过dky表的mac去mf表中拿到mf的行号
        for  key, val in mac_mf_dict.items():
            if val == mac_dky_dict[num]:
                num_time_mac_mf.update({key:[val, mac_time[num]]})     # 取值为最后一个相同mac的时间
        #3.在mf表中，根据刚刚获取的mac地址和mf的行号在后面拼接一列time（dky.time）;
        #在mf表中，拼接一列time（dky.time）;
        #

    # for mac in mac_match:


    return num_time_mac_mf

def macs_list():

    # 生成MAC列表,字典
    global MAC1_dict, MAC2_dict
    MAC1 = list()
    MAC2 = list()
    MAC1_dict = {}  # 表一
    MAC2_dict = {}  # 表二
    for row in worksheet_excel1["B"]:
        if row.value != None:
            MAC1.append(row.value)
            MAC1_dict.update({row.coordinate:row.value})

    for row in worksheet_excel2["H"]:
        if row.value != None:
            string = row.value
            string = string.replace("-", "")
            MAC2.append(string)
            MAC2_dict[row.coordinate] = string

    # 清除特定字符串
    global cleaned_list1, cleaned_list2
    cleaned_list1 = [s.strip("\u200e") for s in MAC1]
    cleaned_list2 = [s.strip("\u200e") for s in MAC2]

def diff():
    # 比对网关数据全量列表和申请统计表中个人证书的Mac地址，定义额外列表extra
    extra = []
    extra2 = []
    global keys1, keys2
    keys1 = []
    keys2 = []

    if int(str.split(' ')[0]) == 1:

        # 表格1有的，表格2没有的 extra
        for mac_excel1 in cleaned_list1:
            if mac_excel1 not in cleaned_list2:
                extra.append(mac_excel1)
        # 表格2有的，表格1没有的 extra2
        for mac_excel2 in cleaned_list2:
            if mac_excel2 not in cleaned_list1:
                extra2.append(mac_excel2)
        # set1 = set(cleaned_list1)
        # set2 = set(cleaned_list2)
        # extra = list(set1 - set2)
        # extra2 = list(set2.difference(set1))
    # 获取数据坐标。
        for key, val in MAC1_dict.items():
            for value in extra:
                if val == value:
                    keys1.append(key)

        for key, val in MAC2_dict.items():
            for value in extra2:
                if val == value:
                    keys2.append(key)

    elif int(str.split(' ')[0]) == 3:
        for mac_excel in cleaned_list1:
            if mac_excel in cleaned_list2:
                extra.append(mac_excel)

            elif mac_excel not in cleaned_list2:
                extra2.append(mac_excel)
        # 获取数据坐标。
        for key, val in MAC2_dict.items():
            for value in extra:
                if val == value:
                    keys1.append(key)  #  表二单元格的坐标

        for key, val in MAC1_dict.items():
            for value in extra2:
                if val == value:
                    keys2.append(key)

def colors_high():
    # 高亮颜色显示, 并且分表保存

    global workbook3
    if int(str.split(' ')[0]) == 1:
        workbook3 = openpyxl.load_workbook(f'{tmp_table}')

        # source_sheet = workbook3[workbook3.总表[0]]

        # 创建新工作表
        # new_sheet = workbook3.create_sheet("总表2")
        #
        # # 复制源工作表的数据到新工作表
        # for row in source_sheet.iter_rows():
        #     new_row = [cell.value for cell in row]
        #     new_sheet.append(new_row)

        worksheet_excel6 = workbook3["没有记录的数据"]
        worksheet_excel7 = workbook3["网关没有的数据"]
        # for coordinate in keys1:  # 表一
        #     row = coordinate[1:]
        #     target_row = worksheet_excel1[row]
        #     highlight_style = PatternFill(fill_type='solid', fgColor='FFFAFA')
        #     for cell in target_row:
        #         if cell.column <= 10:
        #             cell.fill = highlight_style
        #  复制数据到新表
        for coordinate in keys1:  # 表一
            row = coordinate[1:]
            target_row = worksheet_excel1[row]
            for cell in target_row:
                dst_cell = worksheet_excel6.cell(row=cell.row, column=cell.column, value=cell.value)
                if cell.has_style:
                    dst_cell.font = copy(cell.font)
                    # dst_cell.fill = copy(cell.fill)
                    dst_cell.border = copy(cell.border)
                    dst_cell.number_format = copy(cell.number_format)
                    dst_cell.protection = copy(cell.protection)
                    dst_cell.alignment = copy(cell.alignment)
        rows = list(worksheet_excel6.iter_rows())
        for row in reversed(rows):
            # 判断行是否为空行（所有单元格都为空）
            if all(cell.value is None for cell in row):
                # 删除空行
                worksheet_excel6.delete_rows(row[0].row, 1)

        # for coordinate in keys2:  # 表二
        #     row = coordinate[1:]
        #     target_row = worksheet_excel2[row]
        #     highlight_style = PatternFill(fill_type='solid', fgColor='00FF00')
        #     for cell in target_row:
        #         if cell.column <= 10:
        #             cell.fill = highlight_style

        for coordinate in keys2:  # 表二
            row = coordinate[1:]
            target_row = worksheet_excel2[row]
            for cell in target_row:
                dst_cell = worksheet_excel7.cell(row=cell.row, column=cell.column, value=cell.value)
                if cell.has_style:
                    dst_cell.font = copy(cell.font)
                    # dst_cell.fill = copy(cell.fill)
                    dst_cell.border = copy(cell.border)
                    dst_cell.number_format = copy(cell.number_format)
                    dst_cell.protection = copy(cell.protection)
                    dst_cell.alignment = copy(cell.alignment)
        rows = list(worksheet_excel7.iter_rows())
        for row in reversed(rows):
            # 判断行是否为空行（所有单元格都为空）
            if all(cell.value is None for cell in row):
                # 删除空行
                worksheet_excel7.delete_rows(row[0].row, 1)




    elif int(str.split(' ')[0]) == 3:
        worksheet_excel5 = workbook3.create_sheet("未登录", 3)
        #  执行染色
        for coordinate in keys1:  # 表一
            row = coordinate[1:]
            target_row = worksheet_excel2[row]
            highlight_style = PatternFill(fill_type='solid', fgColor='FFFF00')
            for cell in target_row:
                if cell.column <= 10:
                    cell.fill = highlight_style
        #  复制数据到新表“未登录”
        for coordinate in keys1:  # 表一
            row = coordinate[1:]
            target_row = worksheet_excel2[row]
            for cell in target_row:
                dst_cell = worksheet_excel5.cell(row=cell.row, column=cell.column, value=cell.value)
                if cell.has_style:
                    dst_cell.font = copy(cell.font)
                    # dst_cell.fill = copy(cell.fill)
                    dst_cell.border = copy(cell.border)
                    dst_cell.number_format = copy(cell.number_format)
                    dst_cell.protection = copy(cell.protection)
                    dst_cell.alignment = copy(cell.alignment)
            # 遍历工作表中的每一行
        rows = list(worksheet_excel5.iter_rows())
        for row in reversed(rows):
            # 判断行是否为空行（所有单元格都为空）
            if all(cell.value is None for cell in row):
                # 删除空行
                worksheet_excel5.delete_rows(row[0].row, 1)
        # rows_to_delete = [] # 定义空行列表
        # for row5 in worksheet_excel5.iter_rows(min_row=2, max_row=worksheet_excel5.max_row,
        #                                       min_col=1):
        #     for cell in row5:
        #         if cell.value is None:
        #             rows_to_delete.append(row5[0].row)
        #  删除空行
        # for row_idx in sorted(rows_to_delete, reverse=True):
        #     worksheet_excel5.delete_rows(row_idx)
        # print(rows_to_delete)
            #     if all(cell.value is None for cell in row5):
        #         worksheet_excel5.delete_rows(row5[0].row)
        #         print(row5[0].row)
        # 获取默认的工作表
        default_sheet = workbook3.active

        # 删除默认的工作表
        workbook3.remove(default_sheet)
        worksheet_execl3 = workbook3.create_sheet("总表", 4)
        workbook3.create_sheet("没有记录的数据", 1)
        workbook3.create_sheet("网关没有的数据", 2)
        # 创建sheet表
        # for coordinate in keys2:  # 表二
            # row = coordinate[1:]

            # target_row = worksheet_excel1[row]
            # highlight_style = PatternFill(fill_type='solid', fgColor='00FF00') # 染色：黄色
        for row in worksheet_excel2.iter_rows(values_only=False):
            for cell in row:
                # 复制值和样式
                dst_cell = worksheet_execl3.cell(row=cell.row, column=cell.column, value=cell.value)
                if cell.has_style:
                    dst_cell.font = copy(cell.font)
                    dst_cell.fill = copy(cell.fill)
                    dst_cell.border = copy(cell.border)
                    dst_cell.number_format = copy(cell.number_format)
                    dst_cell.protection = copy(cell.protection)
                    dst_cell.alignment = copy(cell.alignment)
            # worksheet_execl3.append(row)
                # for cell1 in row:
                #     for cell2 in target_row:
                #         # cell.fill = highlight_style
                #         cell1.value = cell2.value

        # workbook3.save('C:\\Users\\11027\\Desktop\\test3.xlsx')
# # 保存
def save(*args):
    if len(args) == 1:
        workbook.save(args[0])
    elif len(args) == 2:
        workbook.save(args[0])
        workbook2.save(args[1])

def create_table():

    worksheet_excel4 = workbook3.create_sheet("证书到期时间")
    worksheet_excel2.insert_cols(10)

    # 设置新列的值


    for row1 in worksheet_excel2.iter_rows(values_only=False):#(min_row=2, max_col=10, max_row=worksheet_excel2.max_row):
        for cell in row1:
            # 复制值和样式
            dst_cell = worksheet_excel4.cell(row=cell.row, column=cell.column, value=cell.value)
            if cell.has_style:
                dst_cell.font = copy(cell.font)
                dst_cell.fill = copy(cell.fill)
                dst_cell.border = copy(cell.border)
                dst_cell.number_format = copy(cell.number_format)
                dst_cell.protection = copy(cell.protection)
                dst_cell.alignment = copy(cell.alignment)
        # worksheet_excel4.append(row1)
    for row1 in worksheet_excel4.iter_rows(min_row=2, max_col=10, max_row=worksheet_excel2.max_row):
        # for row in worksheet_excel4.iter_rows(min_row=1, max_row=worksheet_excel2.max_row, min_col=1, max_col=10):
            for cell1 in row1:
                if cell1.column == 10 and int(cell1.coordinate[1:]) in list(num_time_mac_mf.keys()):  # 判断是否为新插入的列
                    cell1.value = num_time_mac_mf[int(cell1.coordinate[1:])][1]
                elif int(cell1.coordinate[1:]) not in list(num_time_mac_mf.keys()):
                    cell1.fill = PatternFill(fill_type='solid', fgColor='00FF00')

    # 保存Excel文件
    # workbook3.copy_worksheet(workbook2.active)
    # workbook3.insert_sheet(workbook3.get_sheet_by_name('Sheet1'), after=workbook3.get_sheet_by_name('Sheet1'))
    # workbook3['Sheet1'].title = workbook3.copy_worksheet(workbook2.active).title
    # for row in workbook3.copy_worksheet(workbook2.active).iter_rows(values_only=True):
    #     workbook3['Sheet1'].append(row)
    workbook3.save('C:\\Users\\11027\\Desktop\\time.xlsx')
# print("=" * 50)
# print(cleaned_list1)
# print(cleaned_list2)

    # if row != None:
# 选择第3行
# target_row = worksheet[3]

# # 创建样式对象
# highlight_style = PatternFill(fill_type='solid', fgColor='FFFF00')
#
# # 设置目标行的样式
# for cell in target_row:
#     cell.fill = highlight_style
if __name__ == '__main__':
    path = 'C:\\Users\\11027\\Desktop\\'
    #
    for file_path in [f'{path}test.xlsx', f'{path}test2.xlsx', f'{path}time.xlsx']:
        delete_file(file_path)
    seclect_sheet(f'{path}陕西1 - 副本_提交给陕西的.xlsx',
                  f'{path}00_申请统计.xlsx')
    col_all()
    time_dky()
    create_table()
    tmp_table = path + "time.xlsx"
    # print(tmp_table)
    while True:
    #     print(f'{tmp_table}')
        seclect_sheet(f'{path}2024-03-05网厂网关数据.xlsx', f'{tmp_table}')
        macs_list()
        diff()
        colors_high()
        workbook3.save('C:\\Users\\11027\\Desktop\\test3.xlsx')
        tmp_table = 'C:\\Users\\11027\\Desktop\\test3.xlsx'
        # save('C:\\Users\\11027\\Desktop\\test.xlsx', 'C:\\Users\\11027\\Desktop\\test2.xlsx')
